﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Data;
using System.Web.Services.Description;
using MockPrjTeam2.Common;
using MockPrjTeam2.Entity;

namespace MockPrjTeam2.DAO
{
    public class TrustDistrictDao
    {
        private static TrustDistrictDao _trustDistrictDao;
        public static TrustDistrictDao Instance
        {
            get
            {
                if (_trustDistrictDao == null)
                    _trustDistrictDao = new TrustDistrictDao();

                return _trustDistrictDao;
            }
        }

        //method GetAllTrustDistrict()
        public DataTable GetAll()
        {
            const string query = "Select TrustDistrictName,DescriptionDistrict,TrustDistrict.IsActive,TrustRegion.TrustRegionName,TrustDistrictID from TrustDistrict inner join TrustRegion on TrustDistrict.TrustRegionID=TrustRegion.TrustRegionID where TrustDistrict.IsActive=1 order by TrustDistrictID asc";
            using (var helper = new SqlHelper())
            {
                return helper.ExecuteSelectQuery(query);
            }
        }

        //method GetTrustDistrictInActive()
        public DataTable GetInActive(int id)
        {

            string query = "Select TrustDistrictName,DescriptionDistrict,TrustDistrict.IsActive,TrustRegion.TrustRegionName,TrustDistrictID from TrustDistrict inner join TrustRegion on TrustDistrict.TrustRegionID=TrustRegion.TrustRegionID WHERE TrustRegion.TrustRegionID = @ID order by TrustDistrictID asc";
            var sqlParameter = new SqlParameter[1];
            sqlParameter[0] = new SqlParameter{ ParameterName = "@ID", Value = id};
            using (var helper = new SqlHelper())
            {
                return helper.ExecuteSelectQuery(query,sqlParameter);
            }
        }

        public bool SetInActive(int trustDistrictId, bool status)
        {
            bool reply;
            string query = @"update TrustDistrict set IsActive=@IsActive where TrustDistrictID=@TrustDistrictID ";
            using (var helper = new SqlHelper())
            {
                SqlParameter[] sqlParameters = new SqlParameter[2];
                sqlParameters[0] = new SqlParameter("@TrustDistrictID", SqlDbType.Int);
                sqlParameters[0].Value = trustDistrictId;
                sqlParameters[1] = new SqlParameter("@IsActive", SqlDbType.Bit);
                sqlParameters[1].Value = status;
                reply = helper.ExecuteUpdateQuery(query, sqlParameters);
            }
            return reply;
        }

        
        public DataTable StartWithByNames(string startWith, int flag, int id)
        {
            string query = @"Select TrustDistrictID,TrustDistrictName,DescriptionDistrict,TrustDistrict.IsActive,TrustRegion.TrustRegionName from TrustDistrict inner join TrustRegion on TrustDistrict.TrustRegionID=TrustRegion.TrustRegionID";
            if (flag == 1)
            {
                query = startWith.Equals("all") ? query + " WHERE TrustRegion.TrustRegionID = @ID order by TrustDistrict.TrustDistrictName asc" : query +
                    " WHERE  (TrustDistrict.TrustDistrictName LIKE '['+@TrustDistrictName+']%') AND TrustRegion.TrustRegionID = @ID" +
                    " order by TrustDistrict.TrustDistrictName asc";
            }
            else
            {
                query = startWith.Equals("all") ? query +
                    " WHERE TrustDistrict.IsActive = 1 AND TrustRegion.TrustRegionID = @ID order by TrustDistrict.TrustDistrictName asc" : query +
                    " WHERE  (TrustDistrict.TrustDistrictName LIKE '['+@TrustDistrictName+']%') And TrustDistrict.IsActive = 1 AND TrustRegion.TrustRegionID = @ID" +
                    " order by TrustDistrict.TrustDistrictName asc";
            }
            var sqlParameters = new SqlParameter[2];
            sqlParameters[0] = new SqlParameter("@TrustDistrictName", SqlDbType.NVarChar) { Value = startWith };
            sqlParameters[1] = new SqlParameter("@ID", SqlDbType.Int) { Value = id };
            using (var helper = new SqlHelper())
            {
                return helper.ExecuteSelectQuery(query, sqlParameters);
            }
        }

        //method SetActiveTrustDistrict
        public bool SetActive(int trustDistrictId ,bool status)
        {
            bool reply;
            string query = @"update TrustDistrict set IsActive=@IsActive where TrustDistrictID=@TrustDistrictID";
            using (var helper = new SqlHelper())
            {
                SqlParameter[] sqlParameters = new SqlParameter[2];
                sqlParameters[0] = new SqlParameter("@TrustDistrictID", SqlDbType.Int);
                sqlParameters[0].Value = trustDistrictId;
                sqlParameters[1] = new SqlParameter("@IsActive", SqlDbType.Bit);
                sqlParameters[1].Value = status;
                reply = helper.ExecuteUpdateQuery(query, sqlParameters);
            }
            return reply;
        }

        //method GetTrustDistrictById()
        public DataTable GetById(int idTrustDistrict)
        {
            string query =
               "select TrustDistrictName,DescriptionDistrict,TrustRegion.TrustRegionName,TrustDistrict.TrustRegionID,TrustDistrictID from TrustDistrict inner join TrustRegion on TrustDistrict.TrustRegionID=TrustRegion.TrustRegionID where TrustDistrictID=@Id";

            SqlParameter[] sqlParameters = new SqlParameter[1];
            sqlParameters[0] = new SqlParameter("@Id", SqlDbType.Int);
            sqlParameters[0].Value = idTrustDistrict;
            using (var helper = new SqlHelper())
            {
                return helper.ExecuteSelectQuery(query, sqlParameters);
            }
        }
        //method GetTrustDistrictByTrustRegionId()
        public DataTable GetById1(int idTrustDistrict)
        {
            string query = @"select TrustDistrictName,DescriptionDistrict,TrustRegion.TrustRegionName,TrustDistrict.TrustRegionID,TrustDistrictID, TrustDistrict.IsActive from TrustDistrict inner join TrustRegion on TrustDistrict.TrustRegionID=TrustRegion.TrustRegionID where TrustDistrict.TrustRegionID=@Id And TrustDistrict.IsActive = 1";

            SqlParameter[] sqlParameters = new SqlParameter[1];
            sqlParameters[0] = new SqlParameter("@Id", SqlDbType.Int);
            sqlParameters[0].Value = idTrustDistrict;
            using (var helper = new SqlHelper())
            {
                return helper.ExecuteSelectQuery(query, sqlParameters);
            }
        }

        //method UpdateTrustDistrict
        public bool UpdateTrustDistrict(TrustDistrict trustDistrict)
        {
            bool reply = false;
            string query =
                @"update TrustDistrict set TrustDistrictName=@TrustDistrictName,DescriptionDistrict=@DescriptionDistrict where TrustDistrict.TrustDistrictID=@TrustDistrictID ";

            using (var helper = new SqlHelper())
            {
                SqlParameter[] sqlParameters = new SqlParameter[4];

                sqlParameters[0] = new SqlParameter("@TrustDistrictName", SqlDbType.NVarChar);
                sqlParameters[0].Value = trustDistrict.TrustDistrictName;
                sqlParameters[1] = new SqlParameter("@DescriptionDistrict", SqlDbType.NVarChar);
                sqlParameters[1].Value = trustDistrict.DescriptionDistrict;
                sqlParameters[2] = new SqlParameter("@TrustDistrictID", SqlDbType.Int);
                sqlParameters[2].Value = trustDistrict.TrustDistrictID;
                sqlParameters[3] = new SqlParameter("@TrustRegionID", SqlDbType.NVarChar);
                sqlParameters[3].Value = trustDistrict.TrustRegionID;
                
                reply = helper.ExecuteUpdateQuery(query, sqlParameters);
            }
            return reply;
        }

        //method AddTrustDistrict()
        public bool AddTrustDistrict(TrustDistrict trustDistrict)
        {
            int trustDistrictId;
            bool reply = false;
            string query = @"insert into TrustDistrict
            (TrustRegionID,TrustDistrictName,DescriptionDistrict,IsActive) 
            values (@TrustRegionID,@TrustDistrictName,@DescriptionDistrict,@IsActive)";
            using (var helper = new SqlHelper())
            {
                SqlParameter[] sqlParameters = new SqlParameter[4];

                sqlParameters[0] = new SqlParameter("@TrustDistrictName", SqlDbType.NVarChar);
                sqlParameters[0].Value = trustDistrict.TrustDistrictName;
                sqlParameters[1] = new SqlParameter("@DescriptionDistrict", SqlDbType.NVarChar);
                sqlParameters[1].Value = trustDistrict.DescriptionDistrict;
                sqlParameters[2] = new SqlParameter("@TrustRegionID", SqlDbType.Int);
                sqlParameters[2].Value = trustDistrict.TrustRegionID;
                sqlParameters[3]=new SqlParameter{ParameterName = "@IsActive",
                    Value = trustDistrict.IsActive=true,DbType = DbType.Boolean};

                reply = helper.ExecuteInsertQuery(query, sqlParameters);
                }
            return reply;
        }

        
        //method CheckTrustDistrictNameExist(
        public int CheckNameExist(string trustDistrictName)
        {
            string query = "select count(TrustDistrictName) from TrustDistrict where TrustDistrictName=@TrustDistrictName";
            using (var helper = new SqlHelper())
            {
                SqlParameter[] sqlParameters = new SqlParameter[1];
                sqlParameters[0] = new SqlParameter("@TrustDistrictName", SqlDbType.NVarChar);
                sqlParameters[0].Value = trustDistrictName;
                return helper.ExecuteScalar(query, sqlParameters);
            }
        }
    }
}